﻿using Microsoft.EntityFrameworkCore;
using MySqlConnector;
using System.Data;
using System.Linq.Expressions;
using System.Text;
using Yz.Core.Tools;

namespace Yz.DB.EFCore.DBUtility
{
    public static class EFMySqlExtension
    {
        /// <summary>
        /// Databale转换成csv
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public static string ToCsv(this DataTable table)
        {
            StringBuilder sb = new StringBuilder();
            DataColumn colum;
            foreach (DataRow row in table.Rows)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    colum = table.Columns[i];
                    if (i != 0)
                    {
                        sb.Append(",");
                    }
                    if (colum.DataType == typeof(string))
                    {
                        var vStr = row[colum].ToString();
                        if (vStr.Contains("\""))
                        {
                            vStr = vStr.Replace("\"", "\"\"");
                        }
                        if (vStr.Contains(",") || vStr.Contains("\r\n") || vStr.Contains("\n"))
                        {
                            vStr = $"\"{vStr}\"";
                        }
                        sb.Append(vStr);
                    }
                    else
                    {
                        sb.Append(row[colum]);
                    }
                }
                sb.Append(EFExtension.IsWin() ? "\r\n" : "\n");
            }
            return sb.ToString();
        }
        /// <summary>
        ///     批量插入数据
        /// </summary>
        /// <typeparam name="T">需求更新的实体</typeparam>
        /// <param name="list">数据源</param>
        /// <param name="dbContext">数据库实例</param>
        public static int BulkInsertMySql<T>(this IList<T> list, DbContext dbContext, bool hasKey = false)
        {
            var sucCount = list.Count;
            if (sucCount == 0)
            {
                return 0;
            }
            var sourcedt = list.ToDataTable(hasKey);
            string csv = sourcedt.ToCsv();
            var entityMap = EFExtension.GetEntityMap(typeof(T), dbContext);
            if (entityMap == null)
            {
                throw new ArgumentException("Could not load the entity mapping information for the source.", "source");
            }
            #region MySqlBulkLoader 
            try
            {
                var columns = new List<string>();
                foreach (DataColumn item in sourcedt.Columns)
                {
                    string columnName = entityMap.PropertyMaps.Where(p => p.PropertyName == item.ColumnName).Select(p => p.ColumnName).FirstOrDefault();
                    if (!string.IsNullOrEmpty(columnName))
                    {
                        columns.Add(columnName);
                    }
                }
                byte[] array = Encoding.UTF8.GetBytes(csv);
                MemoryStream stream = new MemoryStream(array);
                var conn = dbContext.Database.GetDbConnection() as MySqlConnection;
                var loader = new MySqlBulkLoader(conn)
                {
                    SourceStream = stream,
                    Local = true,
                    LineTerminator = EFExtension.IsWin() ? "\r\n" : "\n",
                    FieldTerminator = ",",
                    FieldQuotationCharacter = '"',
                    EscapeCharacter = '"',
                    TableName = entityMap.TableName,
                    CharacterSet = "UTF8"
                };
                loader.Columns.AddRange(columns);
                sucCount = loader.Load();
                return sucCount;
            }
            catch (Exception ex)
            {
                YzNLog.Error(ex, "批量新增");
                return -1;
            }
            finally
            {
                sourcedt.Clear();
                sourcedt.Dispose();
            }

            #endregion MySqlBulkLoader

        }
        /// <summary>
        ///     批量插入数据
        /// </summary>
        /// <typeparam name="T">需求更新的实体</typeparam>
        /// <param name="list">数据源</param>
        /// <param name="dbContext">数据库实例</param>
        public static async Task<int> BulkInsertMySqlAsync<T>(this IList<T> list, DbContext dbContext, bool hasKey = false)
        {
            var sucCount = list.Count;
            if (sucCount == 0)
            {
                return 0;
            }
            var sourcedt = list.ToDataTable(hasKey);
            string csv = sourcedt.ToCsv();
            var entityMap = EFExtension.GetEntityMap(typeof(T), dbContext);
            if (entityMap == null)
            {
                throw new ArgumentException("Could not load the entity mapping information for the source.", "source");
            }
            #region MySqlBulkLoader 
            try
            {
                var columns = new List<string>();
                foreach (DataColumn item in sourcedt.Columns)
                {
                    string columnName = entityMap.PropertyMaps.Where(p => p.PropertyName == item.ColumnName).Select(p => p.ColumnName).FirstOrDefault();
                    if (!string.IsNullOrEmpty(columnName))
                    {
                        columns.Add(columnName);
                    }
                }
                byte[] array = Encoding.UTF8.GetBytes(csv);
                MemoryStream stream = new MemoryStream(array);
                var conn = dbContext.Database.GetDbConnection() as MySqlConnection;
                var loader = new MySqlBulkLoader(conn)
                {
                    SourceStream = stream,
                    Local = true,
                    LineTerminator = EFExtension.IsWin() ? "\r\n" : "\n",
                    FieldTerminator = ",",
                    FieldQuotationCharacter = '"',
                    EscapeCharacter = '"',
                    TableName = entityMap.TableName,
                    CharacterSet = "UTF8"
                };
                loader.Columns.AddRange(columns);
                sucCount = await loader.LoadAsync();
                return sucCount;
            }
            catch (Exception ex)
            {
                YzNLog.Error(ex, "批量新增");
                return -1;
            }
            finally
            {
                sourcedt.Clear();
                sourcedt.Dispose();
            }

            #endregion MySqlBulkLoader

        }

        /// <summary>
        ///     批量修改数据
        /// </summary>
        /// <typeparam name="TEntity">需求更新的实体</typeparam>
        /// <param name="list">数据源</param>
        /// <param name="dbContext">数据库实例</param>
        /// <param name="updateExpression">更新的字段信息</param>
        /// <returns></returns>
        public static int BulkUpdateMySql<TEntity>(this IList<TEntity> list, DbContext dbContext,
            Expression<Func<TEntity, TEntity>> updateExpression)
            where TEntity : class
        {
            var connString = dbContext.Database.GetDbConnection().ConnectionString;
            var sucCount = list.Count;
            if (sucCount == 0)
            {
                return 0;
            }
            var sourcedt = list.ToDataTable();
            if (updateExpression == null)
            {
                throw new ArgumentNullException("updateExpression");
            }
            var entityMap = EFExtension.GetEntityMap(typeof(TEntity), dbContext);
            if (entityMap == null)
            {
                throw new ArgumentException("Could not load the entity mapping information for the source.", "source");
            }
            var conn = new MySqlConnection(connString);
            var comm = conn.CreateCommand();
            comm.CommandTimeout = 30000;
            comm.CommandType = CommandType.Text;
            conn.Open();
            var tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
            var sql = "select * from " + entityMap.TableName;
            var selcom = new MySqlCommand(sql, conn, tran);
            var adapter = new MySqlDataAdapter(selcom);
            var commandBulider = new MySqlCommandBuilder(adapter)
            {
                ConflictOption = ConflictOption.OverwriteChanges
            };
            try
            {
                var suc = 0;
                var Upcommand = new MySqlCommand();

                #region 生成相关语句

                var sqlBuilder = new StringBuilder();
                sqlBuilder.Append("UPDATE ");
                sqlBuilder.Append(entityMap.TableName);
                sqlBuilder.AppendLine(" SET ");
                if (updateExpression.Body as MemberInitExpression == null)
                {
                    throw new ArgumentException("The update expression must be of type MemberInitExpression.",
                        "updateExpression");
                }

                var wroteSet = false;
                foreach (var binding in (updateExpression.Body as MemberInitExpression).Bindings)
                {
                    if (wroteSet)
                    {
                        sqlBuilder.AppendLine(", ");
                    }
                    var propertyName = binding.Member.Name;
                    var columnName = entityMap.PropertyMaps
                        .Where(p => p.PropertyName == propertyName)
                        .Select(p => p.ColumnName)
                        .FirstOrDefault();

                    var parameter = Upcommand.CreateParameter();
                    parameter.ParameterName = "@" + columnName;
                    parameter.SourceColumn = columnName;
                    Upcommand.Parameters.Add(parameter);
                    sqlBuilder.AppendFormat("[{0}] = @{0}", columnName);
                    wroteSet = true;
                }

                sqlBuilder.AppendLine();
                sqlBuilder.Append(" where ");

                var wroteKey = false;
                foreach (var keyMap in entityMap.KeyMaps)
                {
                    if (wroteKey)
                    {
                        sqlBuilder.Append(" AND ");
                    }
                    var propertyName = keyMap.PropertyName;
                    var columnName = entityMap.PropertyMaps
                        .Where(p => p.PropertyName == propertyName)
                        .Select(p => p.ColumnName)
                        .FirstOrDefault();
                    var parameter = Upcommand.CreateParameter();
                    parameter.ParameterName = "@" + columnName;
                    parameter.SourceColumn = columnName;
                    Upcommand.Parameters.Add(parameter);
                    sqlBuilder.AppendFormat("[{0}] = @{0}", keyMap.ColumnName);
                    wroteKey = true;
                }

                Upcommand.CommandText = sqlBuilder.ToString();

                #endregion 生成相关语句

                adapter.UpdateBatchSize = list.Count;
                sourcedt.AcceptChanges();
                Upcommand.Connection = conn;
                Upcommand.Transaction = tran;
                Upcommand.UpdatedRowSource = UpdateRowSource.None;
                foreach (DataRow dr in sourcedt.Rows)
                {   //所有行设为修改状态
                    dr.SetModified();
                }
                adapter.UpdateCommand = Upcommand;
                suc = adapter.Update(sourcedt);
                sourcedt.AcceptChanges();

                tran.Commit();
                return suc;
            }
            catch (Exception ex)
            {
                YzNLog.Error(ex, "批量修改");
                tran.Rollback();
                return -1;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }

        /// <summary>
        ///     批量修改数据
        /// </summary>
        /// <typeparam name="TEntity">需求更新的实体</typeparam>
        /// <param name="list">数据源</param>
        /// <param name="dbContext">数据库实例</param>
        /// <param name="updateExpression">更新的字段信息</param>
        /// <returns></returns>
        public static async Task<int> BulkUpdateMySqlAsync<TEntity>(this IList<TEntity> list, DbContext dbContext,
            Expression<Func<TEntity, TEntity>> updateExpression)
            where TEntity : class
        {
            var connString = dbContext.Database.GetDbConnection().ConnectionString;
            var sucCount = list.Count;
            if (sucCount == 0)
            {
                return 0;
            }
            var sourcedt = list.ToDataTable();
            if (updateExpression == null)
            {
                throw new ArgumentNullException("updateExpression");
            }
            var entityMap = EFExtension.GetEntityMap(typeof(TEntity), dbContext);
            if (entityMap == null)
            {
                throw new ArgumentException("Could not load the entity mapping information for the source.", "source");
            }
            var conn = new MySqlConnection(connString);
            var comm = conn.CreateCommand();
            comm.CommandTimeout = 30000;
            comm.CommandType = CommandType.Text;
            conn.Open();
            var tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
            var sql = "select * from " + entityMap.TableName;
            var selcom = new MySqlCommand(sql, conn, tran);
            var adapter = new MySqlDataAdapter(selcom);
            var commandBulider = new MySqlCommandBuilder(adapter)
            {
                ConflictOption = ConflictOption.OverwriteChanges
            };
            try
            {
                var suc = 0;
                var Upcommand = new MySqlCommand();

                #region 生成相关语句

                var sqlBuilder = new StringBuilder();
                sqlBuilder.Append("UPDATE ");
                sqlBuilder.Append(entityMap.TableName);
                sqlBuilder.AppendLine(" SET ");
                if (updateExpression.Body as MemberInitExpression == null)
                {
                    throw new ArgumentException("The update expression must be of type MemberInitExpression.",
                        "updateExpression");
                }

                var wroteSet = false;
                foreach (var binding in (updateExpression.Body as MemberInitExpression).Bindings)
                {
                    if (wroteSet)
                    {
                        sqlBuilder.AppendLine(", ");
                    }
                    var propertyName = binding.Member.Name;
                    var columnName = entityMap.PropertyMaps
                        .Where(p => p.PropertyName == propertyName)
                        .Select(p => p.ColumnName)
                        .FirstOrDefault();

                    var parameter = Upcommand.CreateParameter();
                    parameter.ParameterName = "@" + columnName;
                    parameter.SourceColumn = columnName;
                    Upcommand.Parameters.Add(parameter);
                    sqlBuilder.AppendFormat("[{0}] = @{0}", columnName);
                    wroteSet = true;
                }

                sqlBuilder.AppendLine();
                sqlBuilder.Append(" where ");

                var wroteKey = false;
                foreach (var keyMap in entityMap.KeyMaps)
                {
                    if (wroteKey)
                    {
                        sqlBuilder.Append(" AND ");
                    }
                    var propertyName = keyMap.PropertyName;
                    var columnName = entityMap.PropertyMaps
                        .Where(p => p.PropertyName == propertyName)
                        .Select(p => p.ColumnName)
                        .FirstOrDefault();
                    var parameter = Upcommand.CreateParameter();
                    parameter.ParameterName = "@" + columnName;
                    parameter.SourceColumn = columnName;
                    Upcommand.Parameters.Add(parameter);
                    sqlBuilder.AppendFormat("[{0}] = @{0}", keyMap.ColumnName);
                    wroteKey = true;
                }

                Upcommand.CommandText = sqlBuilder.ToString();

                #endregion 生成相关语句

                adapter.UpdateBatchSize = list.Count;
                sourcedt.AcceptChanges();
                Upcommand.Connection = conn;
                Upcommand.Transaction = tran;
                Upcommand.UpdatedRowSource = UpdateRowSource.None;
                foreach (DataRow dr in sourcedt.Rows)
                {   //所有行设为修改状态
                    dr.SetModified();
                }
                adapter.UpdateCommand = Upcommand;
                suc = adapter.Update(sourcedt);
                sourcedt.AcceptChanges();

                await tran.CommitAsync();
                return suc;
            }
            catch (Exception ex)
            {
                YzNLog.Error(ex, "批量修改");
                tran.Rollback();
                return -1;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
    }
}
